﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data.SqlClient;
using Microsoft.SharePoint;
using System.Configuration;
using System.Data;
using Sapiens.at.SharePoint.Logging;
using Sapiens.at.SharePoint.Utils;

namespace Sapiens.at.SharePoint.SQLM
{
    /// <summary>
    /// SQLMatch handles connections, queries and update queries
    /// </summary>
    public static class SQLMatch
    {

        public enum SIUDType { Select, Insert, Update, Delete };

        public static bool isBeforeEvent(SPItemEventProperties properties)
        {
            return (properties.EventType == SPEventReceiverType.FieldAdding |
                properties.EventType == SPEventReceiverType.FieldDeleting |
                properties.EventType == SPEventReceiverType.FieldUpdating |
                properties.EventType == SPEventReceiverType.ItemAdding |
                properties.EventType == SPEventReceiverType.ItemAttachmentAdding |
                properties.EventType == SPEventReceiverType.ItemAttachmentDeleting |
                properties.EventType == SPEventReceiverType.ItemCheckingIn |
                properties.EventType == SPEventReceiverType.ItemCheckingOut |
                properties.EventType == SPEventReceiverType.ItemDeleting |
                properties.EventType == SPEventReceiverType.ItemFileMoving |
                properties.EventType == SPEventReceiverType.ItemUpdating);
        }

        static object GetItemValue(SPWeb web, SPListItem item, SPItemEventProperties properties, string InternalFieldName)
        {
            SPList List = web.Lists[properties.ListId];

            return new DummyEntity(properties, item, web).GetValue(List.Fields.GetFieldByInternalName(InternalFieldName));
            //if (!isBeforeEvent(properties))
            //    return properties.ListItem[InternalFieldName];
            //else
            //{
            //    if (properties.AfterProperties[InternalFieldName] != null)
            //        return properties.AfterProperties[InternalFieldName];
            //    else
            //        return properties.ListItem[InternalFieldName];
            //}
        }

        static void SetItemValue(SPWeb web, SPListItem item, SPItemEventProperties properties, string InternalFieldName, object Value)
        {
            SPList List = web.Lists[properties.ListId];
            new DummyEntity(properties, item, web).SetValue(List.Fields.GetFieldByInternalName(InternalFieldName), Value);
            //if (!isBeforeEvent(properties))
            //    properties.ListItem[InternalFieldName] = Value;
            //else
            //    properties.AfterProperties[InternalFieldName] = Value;
        }

        /// <summary>
        /// Executes all Statements, must be catched!
        /// </summary>
        /// <param name="item"></param>
        /// <param name="properties"></param>
        /// <param name="xmlData"></param>
        /// <returns></returns>
        public static int SQLExecute(SPList list, SPListItem item, SPItemEventProperties properties, XmlData xmlData)
        {
            try
            {
                var web = list.ParentWeb;
                SqlConnection connection = new SqlConnection(xmlData.ConnectionString);
                connection.Open();
                SqlDataAdapter daAdapt = new SqlDataAdapter(xmlData.SQLStatement, connection);
                SqlCommand command = new SqlCommand(xmlData.SQLStatement, connection);
                SIUDType siudType = Enum.GetValues(typeof(SIUDType)).Cast<SIUDType>().FirstOrDefault(st => xmlData.SIUD == st.ToString());
                CommandType commandType = Enum.GetValues(typeof(CommandType)).Cast<CommandType>().FirstOrDefault(ct => xmlData.CommandType == ct.ToString());
                if (siudType == SIUDType.Select)
                {
                    daAdapt.SelectCommand = command;
                    daAdapt.SelectCommand.CommandType = commandType;
                }
                else if (siudType == SIUDType.Insert)
                {
                    daAdapt.InsertCommand = command;
                    daAdapt.InsertCommand.CommandType = commandType;
                }
                else if (siudType == SIUDType.Update)
                {
                    daAdapt.UpdateCommand = command;
                    daAdapt.UpdateCommand.CommandType = commandType;
                }
                else if (siudType == SIUDType.Delete)
                {
                    daAdapt.DeleteCommand = command;
                    daAdapt.DeleteCommand.CommandType = commandType;
                }
                //we always have IN-Params
                int iparam = 1;
                foreach (string param in xmlData.InFieldParamMapping)
                {
                    SqlParameter inparam = command.Parameters.Add("@in" + iparam.ToString(), Enum.GetValues(typeof(SqlDbType)).Cast<SqlDbType>().FirstOrDefault(v => xmlData.InFieldTypeMapping.ElementAt(iparam - 1) == v.ToString()));
                    iparam++;
                    inparam.Direction = ParameterDirection.Input;
                    if (list.Fields.Cast<SPField>().FirstOrDefault(f => f.InternalName == param) != null)
                        if (list.Fields.GetFieldByInternalName(param).Type == SPFieldType.Lookup)
                        {
                            if (inparam.SqlDbType == SqlDbType.Int)
                                inparam.Value = new SPFieldLookupValue(GetItemValue(web, item, properties, param).ToString()).LookupId;
                            else
                                inparam.Value = new SPFieldLookupValue(GetItemValue(web, item, properties, param).ToString()).LookupValue;
                        }
                        else
                        {
                            if (inparam.SqlDbType == SqlDbType.Int)
                                inparam.Value = Convert.ToInt32(GetItemValue(web, item, properties, param));
                            else if (inparam.SqlDbType == SqlDbType.BigInt)
                                inparam.Value = Convert.ToInt64(GetItemValue(web, item, properties, param));
                            else if (inparam.SqlDbType == SqlDbType.SmallInt)
                                inparam.Value = Convert.ToInt16(GetItemValue(web, item, properties, param));
                            else if (inparam.SqlDbType == SqlDbType.DateTime)
                                inparam.Value = Convert.ToDateTime(GetItemValue(web, item, properties, param));
                            else inparam.Value = GetItemValue(web, item, properties, param);
                            if (inparam.Value == null) inparam.Value = DBNull.Value;
                        }
                    else
                        inparam.Value = DBNull.Value;
                }
                //stored procedure & select command, bin outParams
                List<SqlParameter> outPar = new List<SqlParameter>();
                if (siudType == SIUDType.Select & commandType == CommandType.StoredProcedure)
                {
                    int oparam = 1;
                    foreach (string param in xmlData.OutFieldParamMapping)
                    {
                        SqlParameter outparam = command.Parameters.Add("@out" + oparam.ToString(), Enum.GetValues(typeof(SqlDbType)).Cast<SqlDbType>().FirstOrDefault(v => xmlData.OutFieldTypeMapping.ElementAt(oparam - 1) == v.ToString()));
                        oparam++;
                        outparam.Direction = ParameterDirection.Output;
                        outPar.Add(outparam);
                    }
                }

                int rowsaffected = 0;
                if (siudType == SIUDType.Select)
                {
                    SqlDataReader reader = daAdapt.SelectCommand.ExecuteReader(System.Data.CommandBehavior.Default);
                    while (reader.Read())
                    {
                        rowsaffected++;
                        if (commandType == CommandType.Text)
                        {
                            int oparam = 1;
                            foreach (string outparam in xmlData.OutFieldParamMapping)
                            {
                                if (!string.IsNullOrEmpty(outparam))
                                {
                                    try
                                    {
                                        SPField field = list.Fields.GetFieldByInternalName(outparam);
                                        if (field != null)
                                            SetItemValue(web, item, properties, outparam, reader.GetValue(oparam - 1));
                                    }
                                    catch //field does not exist -- no field assigned
                                    { }
                                }
                                oparam++;
                            }
                            break;
                        }
                        if (commandType == CommandType.StoredProcedure)
                        {
                            int oparam = 1;
                            foreach (SqlParameter outparam in outPar)
                            {
                                if (!string.IsNullOrEmpty(xmlData.OutFieldParamMapping[oparam]))
                                {
                                    SetItemValue(web, item, properties, xmlData.OutFieldParamMapping[oparam], outparam.Value);
                                    //Hier fehlt ein Query auf LookupList wenn Target == Lookup und outparam.Value ist ein string
                                }
                                oparam++;
                            }
                            break;
                        }
                    }
                }
                else if (siudType == SIUDType.Insert)
                {
                    rowsaffected = daAdapt.InsertCommand.ExecuteNonQuery();
                }
                else if (siudType == SIUDType.Update)
                {
                    rowsaffected = daAdapt.UpdateCommand.ExecuteNonQuery();
                }
                else if (siudType == SIUDType.Delete)
                {
                    rowsaffected = daAdapt.DeleteCommand.ExecuteNonQuery();
                }
                connection.Close();
                return rowsaffected;
            }
            catch (Exception ex)
            {
                ExceptionHandler.Catch(new SPException(
                    string.Format("Exception in SQLMatch: List:{0} ItemID: {1} ContentTypeID: {2} Event: {3} Receiver: {4} SQL-Statement: {5}",
                    list.RootFolder.Url,
                    item != null ? item.ID.ToString() : "",
                    item != null ? item.ContentTypeId.ToString() : "",
                    properties.EventType.ToString(),
                    properties.ReceiverData,
                    xmlData.SQLStatement), ex), false, properties.Web.Site, true, false);
                return 0;
            }
        }

        public static bool SQLUpdate(SPListItem item, XmlData xmlData)
        {
            return true;
        }


    }
}
